package com.lord.rowcopy.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.io.IOUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;

import com.lord.rowcopy.util.DataHelper;

import cn.hutool.core.io.FileUtil;
import lombok.extern.slf4j.Slf4j;

/**
 * @Description: 操作工具类
 * 该类实现了将一组对象转换为Excel表格，并且可以从Excel表格中读取到一组List对象中
 * 该类利用了BeanUtils框架中的反射完成
 * 使用该类的前提，在相应的实体对象上通过ExcelReources来完成相应的注解
 * @author Lord
 * @date 2019年1月7日
 */
@Slf4j
public class ExcelUtil {
    private static ExcelUtil eu = new ExcelUtil();

    private ExcelUtil() {
    }

    public static ExcelUtil getInstance() {
        return eu;
    }

    /**
     * 处理对象转换为Excel
     * @param template
     * @param objs
     * @param clz
     * @param isClasspath
     * @return
     */
    @SuppressWarnings({ "rawtypes", "unchecked", "unlikely-arg-type" })
    private ExcelTemplate handlerObj2Excel(String template, List objs, Class clz, boolean isClasspath) {
        ExcelTemplate et = ExcelTemplate.getInstance();
        try {
            if (isClasspath) {
                et.readTemplateByClasspath(template);
            } else {
                et.readTemplateByPath(template);
            }
            if (clz != null && "".equals(clz)) {
                List<ExcelHeader> headers = getHeaderList(clz);
                Collections.sort(headers);
                // 输出标题
                et.createNewRow();
                for (ExcelHeader eh : headers) {
                    et.createCell(eh.getTitle());
                }
                // 输出值
                if (DataHelper.isNotEmpty(objs)) {
                    for (Object obj : objs) {
                        et.createNewRow();
                        for (ExcelHeader eh : headers) {
                            et.createCell(BeanUtils.getProperty(obj, getMethodName(eh)));
                        }
                    }
                }
            }
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        }
        return et;
    }

    /**
     * 根据标题获取相应的方法名称
     * @param eh
     * @return
     */
    private String getMethodName(ExcelHeader eh) {
        String mn = eh.getMethodName().substring(3);
        mn = mn.substring(0, 1).toLowerCase() + mn.substring(1);
        return mn;
    }

    /**
     * 将对象转换为Excel并且导出，该方法是基于模板的导出，导出到流
     * @param datas 模板中的替换的常量数据
     * @param template 模板路径
     * @param os 输出流
     * @param objs 对象列表
     * @param clz 对象的类型
     * @param isClasspath 模板是否在classPath路径下
     */
    @SuppressWarnings("rawtypes")
    public void exportObj2ExcelByTemplate(Map<String, String> datas, String template, OutputStream os, List objs,
            Class clz, boolean isClasspath) {
        try {
            ExcelTemplate et = handlerObj2Excel(template, objs, clz, isClasspath);
            et.replaceFinalData(datas);
            et.wirteToStream(os);
            os.flush();
            os.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 将对象转换为Excel并且导出，该方法是基于模板的导出，导出到文件
     * @param datas 模板中的替换的常量数据
     * @param template 模板路径
     * @param tmpPath 生成文件临时存储路径
     * @param fileName 输出文件名称
     * @return
     */
    public File exportObjExcelByTemplate(Map<String, String> datas, String template, String tmpPath, String fileName) {
        try {
            ExcelTemplate et = handlerObj2Excel(template, null, null, true);
            et.replaceFinalData(datas);

            // 将组装好的内容导出至文件
            if (DataHelper.isEmpty(fileName)) {
                fileName = System.currentTimeMillis() + "";
            }

            FileOutputStream fos = new FileOutputStream(tmpPath + "/" + fileName);
            et.wirteToStream(fos);

            fos.flush();
            fos.close();

            File file = new File(tmpPath + "/" + fileName);
            return file;
        } catch (IOException e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 将对象转换为Excel并且导出，该方法是基于模板的导出，导出到文件
     * @param datas 模板中的替换的常量数据
     * @param template 模板路径
     * @param tmpPath 生成文件临时存储路径
     * @param fileName 输出文件名称
     * @return
     */
    public File exportObj2ExcelByTemplate(Map<String, Object> datas, String template, String tmpPath, String fileName) {
        try {
            ExcelTemplate et = handlerObj2Excel(template, null, null, true);
            et.replaceFinalObj(datas);

            // 将组装好的内容导出至文件
            if (DataHelper.isEmpty(fileName)) {
                fileName = System.currentTimeMillis() + "";
            }

            FileOutputStream fos = new FileOutputStream(tmpPath + "/" + fileName);
            et.wirteToStream(fos);

            fos.flush();
            fos.close();

            File file = new File(tmpPath + "/" + fileName);
            return file;
        } catch (IOException e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 将对象转换为Excel并且导出，该方法是基于模板的导出，导出到一个具体的路径中
     * @param datas 模板中的替换的常量数据
     * @param template 模板路径
     * @param outPath 输出路径
     * @param objs 对象列表
     * @param clz 对象的类型
     * @param isClasspath 模板是否在classPath路径下
     */
    @SuppressWarnings("rawtypes")
    public void exportObj2ExcelByTemplate(Map<String, String> datas, String template, String outPath, List objs,
            Class clz, boolean isClasspath) {
        ExcelTemplate et = handlerObj2Excel(template, objs, clz, isClasspath);
        et.replaceFinalData(datas);
        et.writeToFile(outPath);
    }

    /**
     * 将对象转换为Excel并且导出，该方法是基于模板的导出，导出到流,基于Properties作为常量数据
     * @param prop 基于Properties的常量数据模型
     * @param template 模板路径
     * @param os 输出流
     * @param objs 对象列表
     * @param clz 对象的类型
     * @param isClasspath 模板是否在classPath路径下
     */
    @SuppressWarnings("rawtypes")
    public void exportObj2ExcelByTemplate(Properties prop, String template, OutputStream os, List objs, Class clz,
            boolean isClasspath) {
        ExcelTemplate et = handlerObj2Excel(template, objs, clz, isClasspath);
        et.replaceFinalData(prop);
        et.wirteToStream(os);
    }

    /**
     * 将对象转换为Excel并且导出，该方法是基于模板的导出，导出到一个具体的路径中,基于Properties作为常量数据
     * @param prop 基于Properties的常量数据模型
     * @param template 模板路径
     * @param outPath 输出路径
     * @param objs 对象列表
     * @param clz 对象的类型
     * @param isClasspath 模板是否在classPath路径下
     */
    @SuppressWarnings("rawtypes")
    public void exportObj2ExcelByTemplate(Properties prop, String template, String outPath, List objs, Class clz,
            boolean isClasspath) {
        ExcelTemplate et = handlerObj2Excel(template, objs, clz, isClasspath);
        et.replaceFinalData(prop);
        et.writeToFile(outPath);
    }

    @SuppressWarnings("rawtypes")
    private Workbook handleObj2Excel(List objs, Class clz) {
        Workbook wb = new HSSFWorkbook();
        try {
            Sheet sheet = wb.createSheet();
            Row r = sheet.createRow(0);
            List<ExcelHeader> headers = getHeaderList(clz);
            Collections.sort(headers);
            // 写标题
            for (int i = 0; i < headers.size(); i++) {
                r.createCell(i).setCellValue(headers.get(i).getTitle());
            }
            // 写数据
            Object obj = null;
            for (int i = 0; i < objs.size(); i++) {
                r = sheet.createRow(i + 1);
                obj = objs.get(i);
                for (int j = 0; j < headers.size(); j++) {
                    r.createCell(j).setCellValue(BeanUtils.getProperty(obj, getMethodName(headers.get(j))));
                }
            }
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        }
        return wb;
    }

    /**
     * 导出对象到Excel，不是基于模板的，直接新建一个Excel完成导出，基于路径的导出
     * @param outPath 导出路径
     * @param objs 对象列表
     * @param clz 对象类型
     */
    @SuppressWarnings("rawtypes")
    public void exportObj2Excel(String outPath, List objs, Class clz) {
        Workbook wb = handleObj2Excel(objs, clz);
        FileOutputStream fos = null;
        try {
            fos = new FileOutputStream(outPath);
            wb.write(fos);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (fos != null)
                    fos.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 导出对象到Excel，不是基于模板的，直接新建一个Excel完成导出，基于流
     * @param os 输出流
     * @param objs 对象列表
     * @param clz 对象类型
     */
    @SuppressWarnings("rawtypes")
    public void exportObj2Excel(OutputStream os, List objs, Class clz) {
        try {
            Workbook wb = handleObj2Excel(objs, clz);
            wb.write(os);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 从类路径读取相应的Excel文件到对象列表
     * @param path 类路径下的path
     * @param clz 对象类型
     * @param readLine 开始行，注意是标题所在行
     * @param tailLine 底部有多少行，在读入对象时，会减去这些行
     * @return
     */
    @SuppressWarnings("rawtypes")
    public List<Object> readExcel2ObjsByClasspath(String path, Class clz, int readLine, int tailLine) {
        Workbook wb = null;
        try {
            wb = new HSSFWorkbook(TemplateFileUtil.getTemplates(path));
            return handlerExcel2Objs(wb, clz, readLine, tailLine);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 从文件路径读取相应的Excel文件到对象列表
     * @param path 文件路径下的path
     * @param clz 对象类型
     * @param readLine 开始行，注意是标题所在行
     * @param tailLine 底部有多少行，在读入对象时，会减去这些行
     * @return
     */
    @SuppressWarnings("rawtypes")
    public List<Object> readExcel2ObjsByPath(String path, Class clz, int readLine, int tailLine) {
        Workbook wb = null;
        try {
            wb = new HSSFWorkbook(TemplateFileUtil.getTemplates(path));
            return handlerExcel2Objs(wb, clz, readLine, tailLine);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 从类路径读取相应的Excel文件到对象列表，标题行为0，没有尾行
     * @param path 路径
     * @param clz 类型
     * @return 对象列表
     */
    @SuppressWarnings("rawtypes")
    public List<Object> readExcel2ObjsByClasspath(String path, Class clz) {
        return this.readExcel2ObjsByClasspath(path, clz, 0, 0);
    }

    /**
     * 从文件路径读取相应的Excel文件到对象列表，标题行为0，没有尾行
     * @param path 路径
     * @param clz 类型
     * @return 对象列表
     */
    @SuppressWarnings("rawtypes")
    public List<Object> readExcel2ObjsByPath(String path, Class clz) {
        return this.readExcel2ObjsByPath(path, clz, 0, 0);
    }

    /**
     * 获取特殊行下一行坐标
     * @param sheet
     * @param keyword 关键字
     * @return
     */
    public Integer getNextRowIndex(Sheet sheet, String keyword) {
        int beginIndex = 0;
        for (Row row : sheet) {
            for (Cell c : row) {
                String str = c.getStringCellValue().trim();
                if (str.equals(keyword)) {
                    beginIndex = c.getRowIndex() + 1;
                    break;
                }
            }
        }

        return beginIndex;
    }

    /**
     * 获取特殊列坐标
     * @param sheet
     * @param keyword 关键字
     * @return
     */
    public Integer getColumnIndex(Sheet sheet, String keyword) {
        int beginIndex = 0;
        for (Row row : sheet) {
            for (Cell c : row) {
                String str = c.getStringCellValue().trim();
                if (str.equals(keyword)) {
                    beginIndex = c.getColumnIndex();
                    break;
                }
            }
        }

        return beginIndex;
    }

    /**
     * 行复制功能
     * @param wb 工作簿
     * @param fromRow 从哪行开始
     * @param toRow 目标行
     * @param copyValueFlag true则连同cell的内容一起复制
     */
    public void copyRow(Workbook wb, Row fromRow, Row toRow, boolean copyValueFlag) {
        toRow.setHeight(fromRow.getHeight());

        for (Iterator<Cell> cellIt = fromRow.cellIterator(); cellIt.hasNext();) {
            Cell tmpCell = cellIt.next();
            Cell newCell = toRow.createCell(tmpCell.getColumnIndex());
            copyCell(wb, tmpCell, newCell, copyValueFlag);
        }

        Sheet worksheet = fromRow.getSheet();

        for (int i = 0; i < worksheet.getNumMergedRegions(); i++) {
            CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i);
            if (cellRangeAddress.getFirstRow() == fromRow.getRowNum()) {
                CellRangeAddress newCellRangeAddress = new CellRangeAddress(toRow.getRowNum(),
                        (toRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())),
                        cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn());
                worksheet.addMergedRegionUnsafe(newCellRangeAddress);
            }
        }
    }

    /**
     * 复制单元格
     * @param srcCell
     * @param distCell
     * @param copyValueFlag true则连同cell的内容一起复制
     */
    public void copyCell(Workbook wb, Cell srcCell, Cell distCell, boolean copyValueFlag) {
        CellStyle newStyle = wb.createCellStyle();
        CellStyle srcStyle = srcCell.getCellStyle();

        newStyle.cloneStyleFrom(srcStyle);
        newStyle.setFont(wb.getFontAt(srcStyle.getFontIndex()));

        // 样式
        distCell.setCellStyle(newStyle);

        // 内容
        if (srcCell.getCellComment() != null) {
            distCell.setCellComment(srcCell.getCellComment());
        }

        // 不同数据类型处理
        CellType srcCellType = srcCell.getCellTypeEnum();
        distCell.setCellType(srcCellType);

        if (copyValueFlag) {
            if (srcCellType == CellType.NUMERIC) {
                if (DateUtil.isCellDateFormatted(srcCell)) {
                    distCell.setCellValue(srcCell.getDateCellValue());
                } else {
                    distCell.setCellValue(srcCell.getNumericCellValue());
                }
            } else if (srcCellType == CellType.STRING) {
                distCell.setCellValue(srcCell.getRichStringCellValue());
            } else if (srcCellType == CellType.BLANK) {

            } else if (srcCellType == CellType.BOOLEAN) {
                distCell.setCellValue(srcCell.getBooleanCellValue());
            } else if (srcCellType == CellType.ERROR) {
                distCell.setCellErrorValue(srcCell.getErrorCellValue());
            } else if (srcCellType == CellType.FORMULA) {
                distCell.setCellFormula(srcCell.getCellFormula());
            }
        }
    }

    private String getCellValue(Cell c) {
        String o = null;
        switch (c.getCellTypeEnum()) {
            case BLANK:
                o = "";
                break;
            case BOOLEAN:
                o = String.valueOf(c.getBooleanCellValue());
                break;
            case FORMULA:
                o = String.valueOf(c.getCellFormula());
                break;
            case NUMERIC:
                o = String.valueOf(c.getNumericCellValue());
                break;
            case STRING:
                o = c.getStringCellValue();
                break;
            default:
                o = null;
                break;
        }
        return o;
    }

    @SuppressWarnings("rawtypes")
    private List<Object> handlerExcel2Objs(Workbook wb, Class clz, int readLine, int tailLine) {
        Sheet sheet = wb.getSheetAt(0);
        List<Object> objs = null;
        try {
            Row row = sheet.getRow(readLine);
            objs = new ArrayList<Object>();
            Map<Integer, String> maps = getHeaderMap(row, clz);
            if (maps == null || maps.size() <= 0)
                throw new RuntimeException("要读取的Excel的格式不正确，检查是否设定了合适的行");
            for (int i = readLine + 1; i <= sheet.getLastRowNum() - tailLine; i++) {
                row = sheet.getRow(i);
                Object obj = clz.newInstance();
                for (Cell c : row) {
                    int ci = c.getColumnIndex();
                    String mn = maps.get(ci).substring(3);
                    mn = mn.substring(0, 1).toLowerCase() + mn.substring(1);
                    BeanUtils.copyProperty(obj, mn, this.getCellValue(c));
                }
                objs.add(obj);
            }
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        }
        return objs;
    }

    @SuppressWarnings("rawtypes")
    private List<ExcelHeader> getHeaderList(Class clz) {
        List<ExcelHeader> headers = new ArrayList<ExcelHeader>();
        Method[] ms = clz.getDeclaredMethods();
        for (Method m : ms) {
            String mn = m.getName();
            if (mn.startsWith("get")) {
                if (m.isAnnotationPresent(ExcelResources.class)) {
                    ExcelResources er = m.getAnnotation(ExcelResources.class);
                    headers.add(new ExcelHeader(er.title(), er.order(), mn));
                }
            }
        }
        return headers;
    }

    @SuppressWarnings("rawtypes")
    private Map<Integer, String> getHeaderMap(Row titleRow, Class clz) {
        List<ExcelHeader> headers = getHeaderList(clz);
        Map<Integer, String> maps = new HashMap<Integer, String>();
        for (Cell c : titleRow) {
            String title = c.getStringCellValue();
            for (ExcelHeader eh : headers) {
                if (eh.getTitle().equals(title.trim())) {
                    maps.put(c.getColumnIndex(), eh.getMethodName().replace("get", "set"));
                    break;
                }
            }
        }
        return maps;
    }

    /** 
     * Remove a row by its index 
     * @param sheet a Excel sheet 
     * @param rowIndex a 0 based index of removing row 
     */
    public void removeRow(Sheet sheet, int rowIndex) {
        int lastRowNum = sheet.getLastRowNum();
        if (rowIndex >= 0 && rowIndex < lastRowNum)
            // 将行号为rowIndex+1一直到行号为lastRowNum的单元格全部上移一行，以便删除rowIndex行
            sheet.shiftRows(rowIndex + 1, lastRowNum, -1);
        if (rowIndex == lastRowNum) {
            Row removingRow = sheet.getRow(rowIndex);
            if (removingRow != null)
                sheet.removeRow(removingRow);
        }
    }

    /**
     * 删除合并单元格（横向合并）,删除行内所有合并
     * @param sheet
     * @param row 开始行
     * @param column 合并开始列
     */
    public void removeMergedRowRegion(Sheet sheet, int startRow, int endRow, int column) {
        for (int j = startRow; j <= endRow; j++) {
            int sheetMergeCount = sheet.getNumMergedRegions();// 获取所有的合并单元格
            int index = 0;// 用于保存要移除的那个单元格序号
            for (int i = 0; i < sheetMergeCount; i++) {
                CellRangeAddress ca = sheet.getMergedRegion(i); // 获取第i个单元格
                int firstColumn = ca.getFirstColumn();
                int lastColumn = ca.getLastColumn();
                int firstRow = ca.getFirstRow();
                int lastRow = ca.getLastRow();
                if (firstRow >= startRow && lastRow <= endRow) {
                    if (column >= firstColumn && column <= lastColumn) {
                        index = i;
                        sheet.removeMergedRegion(index);// 移除合并单元格
                        break;
                    }
                }
            }
        }
    }

    public void removeMergedRegion(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();// 获取所有的单元格
        int index = 0;// 用于保存要移除的那个单元格序号
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i); // 获取第i个单元格
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    index = i;
                }
            }
        }
        sheet.removeMergedRegion(index);// 移除合并单元格
    }

    /**
     * 使用浏览器下载
     * @param response 响应流
     * @param exportFile 导出文件
     * @param fileName 文件名
     */
    public void setBrowser(HttpServletResponse response, File exportFile, String fileName) {

        try (OutputStream outputStream = response.getOutputStream();
                InputStream inputStream = new FileInputStream(exportFile);) {

            // 清空response
            response.reset();

            // 设置response的Header
            response.setContentType("application/octet-stream");
            response.setHeader("content-type", "application/octet-stream");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + new String(fileName.getBytes("gb2312"), "ISO-8859-1"));

            // 将excel写入到输出流中
            IOUtils.copy(inputStream, outputStream);
            outputStream.flush();

            // 删除生成到本地的临时文件
            FileUtil.del(exportFile);

            log.info("浏览器下载文件成功！文件名：{}", fileName);
        } catch (Exception e) {
            log.info("浏览器下载文件失败！文件名：{}", fileName);
            e.printStackTrace();
        }
    }

}
